﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace TraditionalMalteseCuisine.App_Code.Objects
{
    [Serializable]
    public abstract class BaseTable
    {
        //*************************************************
        //                Class Members
        //*************************************************
        #region Class Members
        public const string ID_TAG = "ID";
        public const string IN_USE_TAG = "in_use";

        private int mID = -1;
        private bool mInUse = true;
        #endregion
        //*************************************************


        //*************************************************
        //               Static Methods
        //*************************************************
        #region Static Methods
        public static OleDbCommand GetCommand_GetAllRecords(string table_name)
        {
            return GetCommand_GetAllRecords(table_name, string.Empty);
        }

        public static OleDbCommand GetCommand_GetAllRecords(string table_name, string order_by)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT * FROM {0} ", table_name);
            if (order_by.Length > 0)
                commandText.AppendFormat("ORDER BY {0} ASC", order_by);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        public static OleDbCommand GetCommand_GetRecordsBy(string table_name, string fieldname, string value)
        {
            return GetCommand_GetRecordsBy(table_name, fieldname, value, string.Empty);
        }

        public static OleDbCommand GetCommand_GetRecordsBy(string table_name, string fieldname, string value, string order_by)
        {
            string[] fieldnames = new string[] { fieldname };
            string[] values = new string[] { value };

            return GetCommand_GetRecordsBy(table_name, fieldnames, values, order_by);
        }

        public static OleDbCommand GetCommand_GetRecordsBy(string table_name, string[] fieldnames, string[] values)
        {
            return GetCommand_GetRecordsBy(table_name, fieldnames, values, string.Empty);
        }

        public static OleDbCommand GetCommand_GetRecordsBy(string table_name, string[] fieldnames, string[] values, string order_by)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT * FROM {0} WHERE (", table_name);

            for (int i = 0; i < fieldnames.Length; i++)
            {
                commandText.AppendFormat("([{0}] = @{0}) AND ", fieldnames[i]);
                oledbCommand.Parameters.AddWithValue(string.Format("@{0}", fieldnames[i]), values[i]);
            }
            commandText.Append(")");
            commandText.Replace(" AND )", ") ");

            if (order_by.Length > 0)
                commandText.AppendFormat("ORDER BY {0} ASC", order_by);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }


        public static OleDbCommand GetCommand_GetCountBy(string table_name, string fieldname, string value)
        {
            string[] fieldnames = new string[] { fieldname };
            string[] values = new string[] { value };

            return GetCommand_GetCountBy(table_name, fieldnames, values);
        }

        public static OleDbCommand GetCommand_GetCountBy(string table_name, string[] fieldnames, string[] values)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT COUNT(*) FROM {0} WHERE (", table_name);

            for (int i = 0; i < fieldnames.Length; i++)
            {
                commandText.AppendFormat("([{0}] = @{0}) AND ", fieldnames[i]);
                oledbCommand.Parameters.AddWithValue(string.Format("@{0}", fieldnames[i]), values[i]);
            }
            commandText.Append(")");
            commandText.Replace(" AND )", ") ");

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }
        #endregion
        //*************************************************


        //*************************************************
        //                   Methods
        //*************************************************
        #region Methods
        public OleDbCommand GetUpdateCommand(string table_name, string fieldname, string new_value)
        {
            string[] fieldnames = new string[] { fieldname };
            string[] new_values = new string[] { new_value };

            return GetUpdateCommand(table_name, fieldnames, new_values);
        }

        public OleDbCommand GetUpdateCommand(string table_name, string[] fieldnames, string[] new_values)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("UPDATE {0} SET ", table_name);

            for (int i = 0; i < fieldnames.Length; i++)
            {
                commandText.AppendFormat("[{0}] = @{0}, ", fieldnames[i]);
                oledbCommand.Parameters.AddWithValue(string.Format("@{0}", fieldnames[i]), new_values[i]);
            }
            
            commandText.Append(")");
            commandText.Replace(", )", " ");

            commandText.AppendFormat("WHERE ([{0}] = @{0})", ID_TAG);
            oledbCommand.Parameters.AddWithValue(string.Format("@{0}", ID_TAG), ID);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        protected abstract string GetInsertScriptUsingParameters();

        public abstract OleDbCommand GetInsertCommand();

        public abstract BaseTable ReadDataRow(DataRow datarow);
        #endregion
        //*************************************************


        //*************************************************
        //                  Properties
        //*************************************************
        #region Properties
        public int ID
        {
            get { return mID; }
            set { mID = value; }
        }

        public bool InUse
        {
            get { return mInUse; }
            set { mInUse = value; }
        }
        #endregion
        //*************************************************
    }
}